package com.example.lin.util;

import com.alibaba.fastjson.JSON;
import com.example.lin.common.BaseExcel;
import com.example.lin.dto.BagDTO;
import com.example.lin.dto.BaseDTO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * @className: ImportUtil
 * @description: 导入工具类信息
 * @author: Lin.Gu
 * @date: 2024/2/6
 */
@Slf4j
public class ImportUtil {


    public static List<List<Object>> readExcelToList(String filePath, int sheet) {
        List<List<Object>> resultList = new LinkedList<>();
        try {
            File file = new File(filePath);
            InputStream in = new FileInputStream(file);
            XSSFWorkbook sheets = new XSSFWorkbook(in);
            XSSFSheet sheetAt = sheets.getSheetAt(sheet);
            XSSFRow headerRow = sheetAt.getRow(0); // 假设第一行为标题行
            for (int i = 1; i <= sheetAt.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheetAt.getRow(i);
                if (Objects.isNull(row)) {
                    break;
                }
                List<Object> objectList = new LinkedList<>();
                for (Cell cell : headerRow) {
                    XSSFCell dataCell = row.getCell(cell.getColumnIndex());
                    //String key = cell.getStringCellValue();
                    Object value = BaseExcel.getString(dataCell);
                    objectList.add(value);
                }
                resultList.add(objectList);
                //System.out.println("处理数据:" + i);
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("读取处理失败:" + e);
        }
        return resultList;
    }


    /**
     * 导入文件返回list
     *
     * @param dto 处理对象
     * @return list
     */
    public static List<Object[]> readExcelList(BaseDTO dto) {
        List<Object[]> dataList = new ArrayList<>(dto.getDataList());
        try {
            File file = new File(dto.getFilePath());
            if (!file.exists()) {
                throw new RuntimeException("文件不存在!");
            }
            InputStream in = new FileInputStream(file);
            // 读取整个Excel
            XSSFWorkbook sheets = new XSSFWorkbook(in);
            // 获取第一个表单Sheet
            XSSFSheet sheetAt = sheets.getSheetAt(dto.getSheet());
            // 从哪一行开始读取数据
            for (int i = dto.getRowNo(); i < sheetAt.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheetAt.getRow(i);
                if (Objects.isNull(row)) {
                    break;
                }
                //必须先获取默认数据  才能取 map数据进行组装合并
                int size = dataList.get(0).length;
                Object[] obj = new Object[size];
                Object ovalue1 = null, ovalue2 = null;
                if (Objects.nonNull(dto.getOneMapIndex())) {
                    ovalue1 = obj[dto.getOneKeyIndex()];
                }
                if (Objects.nonNull(dto.getTwoMapIndex())) {
                    ovalue2 = obj[dto.getTwoKeyIndex()];
                }
                for (int j = 0; j < size; j++) {
                    //默认获取
                    obj[j] = BaseExcel.getString(row.getCell(j));
                    //map信息
                    if (Objects.nonNull(dto.getOneMapIndex()) && dto.getOneMapIndex() == j) {
                        if (ovalue1 instanceof String) {
                            obj[j] = Optional.ofNullable(dto.getOneMap().get((String) obj[dto.getOneKeyIndex()])).orElse("");
                        } else {
                            List<String> listvalue = (List<String>) ovalue1;
                            for (int k = 0; k < listvalue.size(); k++) {
                                obj[j + k] = Optional.ofNullable(listvalue.get(k)).orElse("");
                            }
                        }
                    }
                    if (Objects.nonNull(dto.getTwoMapIndex()) && dto.getTwoMapIndex() == j) {
                        if (ovalue2 instanceof String) {
                            obj[j] = Optional.ofNullable(dto.getTwoMap().get((String) obj[dto.getTwoKeyIndex()])).orElse("");
                        } else {
                            List<String> listvalue = (List<String>) ovalue2;
                            for (int k = 0; k < listvalue.size(); k++) {
                                obj[j + k] = Optional.ofNullable(listvalue.get(k)).orElse("");
                            }
                        }
                    }
                }
                dataList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("读取处理失败:" + e);
        }
        return dataList;
    }


    /**
     * 读取文件输出SQL
     *
     * @param dto 处理对象
     */
    public static void readExcelSout(BaseDTO dto) {
        try {
            File file = new File(dto.getFilePath());
            if (!file.exists()) {
                throw new RuntimeException("文件不存在!");
            }
            InputStream in = new FileInputStream(file);
            // 读取整个Excel
            XSSFWorkbook sheets = new XSSFWorkbook(in);
            // 获取第一个表单Sheet
            XSSFSheet sheetAt = sheets.getSheetAt(dto.getSheet());
            // 从哪一行开始读取数据
            for (int i = dto.getRowNo(); i < sheetAt.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheetAt.getRow(i);
                if (Objects.isNull(row)) {
                    break;
                }
                String key = BaseExcel.getString(row.getCell(0));
                System.out.println("输出:" + key);
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("读取处理失败:" + e);
        }
    }

    /**
     * 读取文件返回map
     *
     * @return map
     */
    public static Map<String, Object> readExcelMap(BaseDTO dto) {
        Map<String, Object> inputMap = new HashMap<>();
        try {
            File file = new File(dto.getFilePath());
            if (!file.exists()) {
                throw new RuntimeException("文件不存在!");
            }
            InputStream in = new FileInputStream(file);
            // 读取整个Excel
            XSSFWorkbook sheets = new XSSFWorkbook(in);
            // 获取第一个表单Sheet
            XSSFSheet sheetAt = sheets.getSheetAt(dto.getSheet());
            // 从哪一行开始读取数据
            for (int i = dto.getRowNo(); i < sheetAt.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheetAt.getRow(i);
                if (Objects.isNull(row)) {
                    break;
                }
                String key = BaseExcel.getString(row.getCell(0));
                //处理list
                if (dto.getMapType() > 0) {
                    List<String> listValue = new ArrayList<>();
                    for (int j = 1; j < dto.getMapType(); j++) {
                        listValue.add(BaseExcel.getString(row.getCell(j)));
                    }
                    inputMap.put(key, listValue);
                } else {
                    String value = BaseExcel.getString(row.getCell(1));
                    inputMap.put(key, value);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("读取处理失败:" + e);
        }
        return inputMap;
    }


    /**
     * 读取数据返回List
     *
     * @param filePath 文件URL
     * @param sheet    表单
     * @param dataList 标头
     * @return 数据
     */
    public static List<Object[]> readExcelList(String filePath, int sheet, List<Object[]> dataList) {
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                throw new Exception("文件不存在!");
            }
            InputStream in = new FileInputStream(file);
            // 读取整个Excel
            XSSFWorkbook sheets = new XSSFWorkbook(in);
            // 获取第一个表单Sheet
            XSSFSheet sheetAt = sheets.getSheetAt(sheet);

            if (Objects.isNull(dataList)) {
                dataList = new ArrayList<>();
                // 获取表头
                XSSFRow headerRow = sheetAt.getRow(0);
                if (headerRow != null) {
                    Object[] rowDataHeader = new Object[headerRow.getLastCellNum()];
                    for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                        rowDataHeader[i] = BaseExcel.getString(headerRow.getCell(i));
                    }
                    dataList.add(rowDataHeader);
                }
            }

            for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheetAt.getRow(i);
                if (Objects.isNull(row)) {
                    break;
                }
                Object[] rowDataHeader = dataList.get(0);
                Object[] rowDataValue = new Object[rowDataHeader.length];
                for (int j = 0; j < rowDataHeader.length; j++) {
                    rowDataValue[j] = BaseExcel.getString(row.getCell(j));
                }
                dataList.add(rowDataValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("读取处理失败:" + e);
        }
        return dataList;
    }

    public static <T> List<T> readExcelDto(Class<T> dtoClass, String filePath, int sheet) {
        List<T> dtoList = new ArrayList<>();
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                throw new Exception("文件不存在!");
            }
            try (InputStream in = new FileInputStream(file)) {
                // 读取整个Excel
                XSSFWorkbook workbook = new XSSFWorkbook(in);
                // 获取第一个表单Sheet
                XSSFSheet sheetAt = workbook.getSheetAt(sheet);

                // 获取表头
                XSSFRow headerRow = sheetAt.getRow(0);
                List<String> headers = new ArrayList<>();
                if (headerRow != null) {
                    for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                        headers.add(BaseExcel.getString(headerRow.getCell(i)));
                    }
                }

                // 遍历数据行
                for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
                    XSSFRow row = sheetAt.getRow(i);
                    if (Objects.isNull(row)) {
                        break;
                    }
                    T dto = dtoClass.getDeclaredConstructor().newInstance();
                    for (int j = 0; j < headers.size(); j++) {
                        String header = headers.get(j);
                        Field field = findField(dtoClass, header);
                        if (field != null) {
                            field.setAccessible(true);
                            String value = BaseExcel.getString(row.getCell(j));
                            setFieldValue(dto, field, value);
                        }
                    }
                    dtoList.add(dto);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("读取处理失败:" + e);
        }
        return dtoList;
    }


    private static Field findField(Class<?> clazz, String fieldName) {
        for (Field field : clazz.getDeclaredFields()) {
            if (field.getName().trim().equals(fieldName.trim())) {
                return field;
            }
        }
        System.out.println("未匹配的字段信息:" + fieldName);
        return null;
    }

    private static void setFieldValue(Object obj, Field field, String value) throws IllegalAccessException {
        Class<?> fieldType = field.getType();
        if (fieldType == String.class) {
            field.set(obj, value);
        } else if (fieldType == int.class || fieldType == Integer.class) {
            field.set(obj, Integer.parseInt(value));
        } else if (fieldType == double.class || fieldType == Double.class) {
            field.set(obj, Double.parseDouble(value));
        }
        // 可以根据需要添加更多类型的处理
    }


    public static void main(String[] args) {
//        System.out.println(readExcelMap(BaseDTO.builder().filePath("C:\\Users\\181596\\Desktop\\测试.xlsx")
//                .mapType(0)
//                .build()));


//       List<Object[]> data = new ArrayList<>();
//        data.add(new Object[]{"单号", "编号", "code", "api"});
//        List<Object[]> objects = readExcelList("C:\\Users\\181596\\Desktop\\大包下推操作.xlsx", 0, null);
//        System.out.println(JSON.toJSON(objects));
//        ExportUtil.exportTheFile("test", objects);


        List<BagDTO> eddDTOS = readExcelDto(BagDTO.class, "C:\\Users\\181596\\Desktop\\大包下推操作.xlsx", 0);
        ExportUtil.exportDtoToExcel(eddDTOS,"bageddd");
    }

}
